<?php

namespace app\admin\controller\record;

use app\admin\model\UserGroup;
use app\common\controller\Backend;
use app\common\model\BaseUser;
use app\common\model\Behavior;
use app\common\model\BehaviorCategory;
use app\common\model\BehaviorRecord;
use app\common\model\BehaviorRecordDetail;
use app\common\model\BehaviorRecordImage;
use app\common\model\User;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use PhpOffice\PhpSpreadsheet\Reader\Csv;
use PhpOffice\PhpSpreadsheet\Reader\Xls;
use PhpOffice\PhpSpreadsheet\Reader\Xlsx;
use think\Db;
use think\Exception;
use think\exception\PDOException;

class Student extends Backend
{
    /**
     * @var \app\common\model\BehaviorRecord
     */
    protected $model = null;
    protected $searchFields = 'name';
    protected $noNeedRight = ['download'];
    protected $relationSearch = true;
    protected $schoolLimit = true;

    protected $categoryType = BehaviorCategory::STUDENT;

    public function _initialize()
    {
        parent::_initialize();
        $this->model = model('BehaviorRecord');
    }

    /**
     * 查看
     */
    public function index()
    {
        //设置过滤方法
        $this->request->filter(['strip_tags']);
        if ($this->request->isAjax()) {

            //如果发送的来源是Selectpage，则转发到Selectpage
            if ($this->request->request('keyField')) {
                return $this->selectpage();
            }

            list($where, $sort, $order, $offset, $limit) = $this->buildparams();

            $total = $this->model->alias('behavior_record')
                ->join('__BEHAVIOR_RECORD_DETAIL__ detail', 'detail.record_id = behavior_record.id')
                ->where($where)
                ->where('behavior_record.behavior_category_type', $this->categoryType)
                ->count();

            $list = $this->model->alias('behavior_record')
                ->join('__BEHAVIOR_RECORD_DETAIL__ detail', 'detail.record_id = behavior_record.id')
                ->join('__BEHAVIOR_CATEGORY__ category', 'category.id = detail.behavior_category_id', 'left')
                ->where($where)
                ->where('behavior_record.behavior_category_type', $this->categoryType)
                ->field('behavior_record.school_id,behavior_record.class_id,behavior_record.teacher_id,behavior_record.student_id,behavior_record.dorm_id,behavior_record.record_username,behavior_record.record_user_group,behavior_record.created_at,behavior_record.json_data,detail.id,detail.record_id,detail.behavior_category_type,detail.behavior_id,detail.behavior_name,detail.behavior_type,detail.unit_score,detail.count,detail.score,category.name behavior_category_name')
                ->order($sort, $order)
                ->limit($offset, $limit)
                ->select();

            if ($list) {
                foreach ($list as $key => $value) {
                    $list[$key]['behavior_type_text'] = isset(Behavior::$typeList[$value['behavior_type']]) ? Behavior::$typeList[$value['behavior_type']] : '';
                    $list[$key]['type'] = '学生行为';
                }
            }

            $result = array("total" => $total, "rows" => $list);

            return json($result);
        }
        return $this->view->fetch();
    }

    /**
     * 删除
     */
    public function del($ids = "")
    {
        if ($ids) {
            $model = model('BehaviorRecordDetail');
            $list = $model->where('id', 'in', $ids)->select();
            $count = 0;
            Db::startTrans();
            try {
                foreach ($list as $k => $v) {
                    $count += $v->delete();
                }
                Db::commit();
            } catch (PDOException $e) {
                Db::rollback();
                $this->error($e->getMessage());
            } catch (Exception $e) {
                Db::rollback();
                $this->error($e->getMessage());
            }
            if ($count) {
                $this->success();
            } else {
                $this->error(__('No rows were deleted'));
            }
        }
        $this->error(__('Parameter %s can not be empty', 'ids'));
    }

    /**
     * 下载模板
     */
    public function download()
    {
        $file_path = 'template/record_student.xlsx';
        $file_dir = ROOT_PATH . 'public' . DS . 'uploads' . DS . "$file_path";    // 下载文件存放目录

        // 检查文件是否存在
        if (!file_exists($file_dir)) {
            $this->error('文件不存在');
        } else {
            // 打开文件
            $file1 = fopen($file_dir, "r");
            // 输入文件标签
            Header("Content-type: application/octet-stream");
            Header("Accept-Ranges: bytes");
            Header("Accept-Length:" . filesize($file_dir));
            Header("Content-Disposition: attachment;filename=批量导入学生模板.xlsx");
            ob_clean();     // 重点！！！
            flush();        // 重点！！！！可以清除文件中多余的路径名以及解决乱码的问题：
            //输出文件内容
            //读取文件内容并直接输出到浏览器
            echo fread($file1, filesize($file_dir));
            fclose($file1);
            exit();
        }
    }

    /**
     * 批量导入
     *
     * @return string|void
     * @throws Exception
     * @throws PDOException
     * @throws \PhpOffice\PhpSpreadsheet\Exception
     * @throws \PhpOffice\PhpSpreadsheet\Reader\Exception
     * @throws \think\db\exception\BindParamException
     * @throws \think\exception\DbException
     */
    public function import()
    {
        if ($this->request->isAjax()) {

            set_time_limit(0);
            ini_set('memory_limit', '512M');
            $file = $this->request->request('file');

            if (!$file) {
                $this->error(__('Parameter %s can not be empty', 'file'));
            }

            $filePath = ROOT_PATH . DS . 'public' . DS . $file;
            if (!is_file($filePath)) {
                $this->error(__('No results were found'));
            }
            //实例化reader
            $ext = pathinfo($filePath, PATHINFO_EXTENSION);
            if (!in_array($ext, ['csv', 'xls', 'xlsx'])) {
                $this->error(__('Unknown data format'));
            }
            if ($ext === 'csv') {
                $file = fopen($filePath, 'r');
                $filePath = tempnam(sys_get_temp_dir(), 'import_csv');
                $fp = fopen($filePath, "w");
                $n = 0;
                while ($line = fgets($file)) {
                    $line = rtrim($line, "\n\r\0");
                    $encoding = mb_detect_encoding($line, ['utf-8', 'gbk', 'latin1', 'big5']);
                    if ($encoding != 'utf-8') {
                        $line = mb_convert_encoding($line, 'utf-8', $encoding);
                    }
                    if ($n == 0 || preg_match('/^".*"$/', $line)) {
                        fwrite($fp, $line . "\n");
                    } else {
                        fwrite($fp, '"' . str_replace(['"', ','], ['""', '","'], $line) . "\"\n");
                    }
                    $n++;
                }
                fclose($file) || fclose($fp);

                $reader = new Csv();
            } elseif ($ext === 'xls') {
                $reader = new Xls();
            } else {
                $reader = new Xlsx();
            }

            // 学校
            $schools = $this->auth->getSchoolColumns();
            // 学校.年级.班级 便于查找
            $classes = \app\common\model\Classes::getMergeClasses($schools);
            if (!$classes) {
                $this->error(__('您账号下没有班级'));
            }

            // 所有点评项
            $behaviors = BehaviorCategory::getMergeBehaviors($schools, $this->categoryType);

            // 点评人
            $users = [];
            $group_users = UserGroup::alias('group')
                ->join('__USER__ user', 'user.group_id = group.id')
                ->where('group.status', 'normal')
                ->whereIn('group.school_id', $this->auth->getSchoolIds())
                ->column('user.id,group.name');
            $base_users = BaseUser::whereIn('id', array_keys($group_users))->column('id record_user_id,nickname record_username,school_id');
            foreach ($base_users as $key => $value) {
                if (isset($group_users[$value['record_user_id']]) && isset($schools[$value['school_id']])) {
                    $users["{$schools[$value['school_id']]}-{$value['record_username']}"] = array_merge($value, ['record_user_group' => $group_users[$value['record_user_id']]]);
                }
            }

            //必填列
            $require_fields = ['学校', '年级', '班级', '名称', '点评分类', '点评人', '点评行为', '点评分值', '创建时间'];

            //加载文件
            $insert = [];
            try {
                if (!$PHPExcel = $reader->load($filePath)) {
                    $this->error(__('Unknown data format'));
                }
                $currentSheet = $PHPExcel->getSheet(0);  //读取文件中的第一个工作表
                $allColumn = $currentSheet->getHighestDataColumn(); //取得最大的列号
                $allRow = $currentSheet->getHighestRow(); //取得一共有多少行
                $maxColumnNumber = Coordinate::columnIndexFromString($allColumn);
                $fields = [];
                for ($currentRow = 2; $currentRow <= 2; $currentRow++) {
                    for ($currentColumn = 1; $currentColumn <= $maxColumnNumber; $currentColumn++) {
                        $val = $currentSheet->getCellByColumnAndRow($currentColumn, $currentRow)->getValue();
                        $fields[] = $val;
                    }
                }

                //检测模板是否完整
                $diff_fields = array_diff($require_fields, $fields);
                if ($diff_fields) {
                    $this->error('模板错误，请重新下载模版');
                }

                for ($currentRow = 3; $currentRow <= $allRow; $currentRow++) {
                    $values = [];
                    for ($currentColumn = 1; $currentColumn <= $maxColumnNumber; $currentColumn++) {
                        $val = $currentSheet->getCellByColumnAndRow($currentColumn, $currentRow)->getValue();
                        $values[] = is_null($val) ? '' : trim($val);
                    }
                    $temp = array_combine($fields, $values);

                    //检测数据完整性
                    foreach ($temp as $key => $value) {
                        if (array_search($key, $require_fields) && !$value) {
                            $this->error("序号：{$temp['序号']} 「{$key}」不能为空");
                        }
                    }

                    $row = [];

                    // 时间格式是否正确
                    $time = strtotime($temp['创建时间']);
                    if ($time) {
                        $temp['创建时间'] = $time;
                    } else {
                        $this->error('「创建时间」格式有误，请设置单元格式为文本');
                    }

                    $record_user_merge = "{$temp['学校']}-{$temp['点评人']}";
                    if (isset($users[$record_user_merge])) {
                        $row += $users[$record_user_merge];
                    } else {
                        $this->error("序号{$temp['序号']} 点评人「{$record_user_merge}」不存在");
                    }

                    $behavior_merge = "{$temp['学校']}-{$temp['点评分类']}-{$temp['点评行为']}";
                    $classes_merge = "{$temp['学校']}-{$temp['年级']}-{$temp['班级']}";

                    if (isset($behaviors[$behavior_merge])) {
                        $row += $behaviors[$behavior_merge];
                        if (isset($classes[$classes_merge])) {
                            $row += $classes[$classes_merge];
                            $student = \app\common\model\Student::where('class_id', $classes[$classes_merge]['id'])
                                ->where('name', $temp['名称'])
                                ->field('id,name,school_id,class_id,number,gender')
                                ->find();
                            if ($student) {
                                $row['student_id'] = $student->id;
                                $row['student_name'] = $student->name;
                                $row['student_number'] = $student->number;
                                $row['student_gender'] = $student->gender;
                            } else {
                                $this->error("序号{$temp['序号']} 学生「{$temp['名称']}」不存在");
                            }
                        } else {
                            $this->error("序号{$temp['序号']} 班级「{$classes_merge}」不存在");
                        }
                    } else {
                        $this->error("序号{$temp['序号']} 行为「{$behavior_merge}」不存在");
                    }

                    if ($row) {
                        $row['school_name'] = $temp['学校'];
                        $row['grade_name'] = $temp['年级'];
                        $row['class_name'] = $temp['班级'];
                        $row['created_at'] = $temp['创建时间'];
                        $insert[] = $row;
                    }
                }

            } catch (Exception $e) {
                $this->error($e->getMessage());
            }

            if (!$insert) {
                $this->error(__('No rows were updated'));
            }

            // 启动事务
            Db::startTrans();
            try {

                foreach ($insert as $key => $value) {
                    $behaviorRecord = BehaviorRecord::create([
                        'record_user_id' => $value['record_user_id'],
                        'record_username' => $value['record_username'],
                        'record_user_group' => $value['record_user_group'],
                        'behavior_category_type' => $value['behavior_category_type'],
                        'school_id' => $value['school_id'],
                        'class_id' => $value['class_id'],
                        'teacher_id' => $value['headmaster_id'],
                        'student_id' => $value['student_id'],
                        'created_at' => $value['created_at'],
                        'comment' => '',
                        'json_data' => json_encode([
                            'school_id' => $value['school_id'],
                            'school_name' => $value['school_name'],
                            'grade_id' => $value['grade_id'],
                            'grade_name' => $value['grade_name'],
                            'class_id' => $value['class_id'],
                            'class_name' => $value['class_name'],
                            'headmaster_id' => $value['headmaster_id'],
                            'headmaster_name' => $value['headmaster_name'],
                            'student_id' => $value['student_id'],
                            'student_name' => $value['student_name'],
                            'student_number' => $value['student_number'],
                            'student_gender' => User::getGender($value['student_gender']),
                        ])
                    ]);
                    BehaviorRecordDetail::create([
                        'record_id' => $behaviorRecord->id,
                        'behavior_category_id' => $value['behavior_category_id'],
                        'behavior_category_type' => $value['behavior_category_type'],
                        'behavior_id' => $value['behavior_id'],
                        'behavior_name' => $value['behavior_name'],
                        'behavior_type' => $value['behavior_type'],
                        'behavior_image_id' => $value['image_id'],
                        'behavior_image_url' => $value['image_url'],
                        'unit_score' => $value['behavior_type'] == 1 ? $value['score'] : $value['score'] * (-1),
                        'count' => 1,
                        'score' => $value['behavior_type'] == 1 ? $value['score'] : $value['score'] * (-1),
                        'is_deduct' => 0,
                        'student_id' => $value['student_id'],
                        'class_id' => $value['class_id']
                    ]);
                }

                // 提交事务
                Db::commit();
            } catch (PDOException $e) {
                // 回滚事务
                Db::rollback();
                $this->error($e->getMessage());
            } catch (\Exception $e) {
                // 回滚事务
                Db::rollback();
                $this->error($e->getMessage());
            }

            $this->success();
        }
        return $this->view->fetch();
    }
}
